# -*- coding: UTF-8 -*-
import os, sys

import pandas as pd

CUR_PATH = os.path.dirname(os.path.abspath(__file__))
PKG_PATH = CUR_PATH[0:CUR_PATH.rindex('\\')]
sys.path.append(PKG_PATH)

from db_interface import db_interface
from scipy import stats
from public_func.exchange_date import cal_stock_exchange_date
import datetime
##  from a_hold_stock_buy import stock_mkv_data
from smart_chance.a_hold_stock_buy import have_stock_percent

def scope_condition_company(ids=""): # ids，行业名称
    """
    ids: 中字头，代表主题、或者是行业名称
    """
    sql = "select a.code, a.name, a.holder_name, a.holder_ratio, b.price, b.pe_ttm, b.mkv, b.ids from " \
          "(select code, name, holder_name, holder_ratio from stock_holder_name) a " \
          "inner join " \
          "(select code, price, ids, pe_ttm, mkv from stock_info where ids = '"+ ids +"') b " \
          "on a.code=b.code"
    if ids == "中字头":
        sql = "select a.code, a.name, a.holder_name, a.holder_ratio,  b.price, b.pe_ttm, b.mkv, '中字头' from " \
          "(select code, name, holder_name, holder_ratio from stock_holder_name " \
          "where holder_name like '%%中国%%' or holder_name like '%%国有%%') a " \
          "inner join " \
          "(select code, price, pe_ttm, mkv from stock_info) b " \
          "on a.code=b.code"
    elif ids == '':
        sql = "select a.code, a.name, a.holder_name, a.holder_ratio, b.price, b.pe_ttm, b.mkv, b.ids from " \
              "(select code, name, holder_name, holder_ratio from stock_holder_name) a " \
              "inner join " \
              "(select code, price, ids, pe_ttm, mkv from stock_info ) b " \
              "on a.code=b.code"
    print(sql)
    query = db_interface.stock_base.select(sql)
    scope_dict = {}
    for item in query:
        code, name, holder_name, hold_ratio, price, pe_dt, mkv, theme = item
        if mkv and mkv >= 15:
            scope_dict[code] = {
                "name": name,
                "holder_name": holder_name,
                "hold_ratio": hold_ratio,
                "price": price,
                "pe_dt": pe_dt,
                "mkv": mkv,
                "theme": theme
            }
    return scope_dict

## 找到前200的数据，然后再开始统计行业。
def stock_roe_rank(scope_dict={}): # scope. dict. 给定某个行业或概念主题，包含其它信息
    scope_dict = scope_condition_company()
    sql = f"select distinct date, code, name,  jzc_syl from basic_finance where type = 'jd' " \
          f"and code in {tuple(scope_dict.keys())} and " \
          f"date >= (" \
          f"select distinct date from basic_finance order by date desc " \
          f"limit 1 offset 8) order by code, date desc"  ## 取9个值，筛选掉1个，剩余8个值。
    sql_data = db_interface.stock_base.select(sql)
    # print(sql)
    roe_dict = {}
    for item in list(sql_data)[:]:
        date, code, name, jzc_syl = item
        date = date.strftime("%Y-%m-%d")
        if code not in roe_dict:
            roe_dict[code] = []
        roe_dict[code].append(
            (name, date, jzc_syl)
        )
    stock_sort = []

    for stock, info in roe_dict.items():
        jd_roe = [round(info[i][2] - info[i+1][2], 2) if info[i+1][1][5:7] != '12'
                  else info[i][2] for i in range(len(info)-1)]
        # print(jd_roe)
        roe_avg = 0
        if len(jd_roe) > 0:
            roe_avg = round(sum(jd_roe)/len(jd_roe), 2)
        if str(stock) in scope_dict:
            scope_data = scope_dict[stock]
            name = scope_data['name']
            holder_name = scope_data['holder_name']
            hold_ratio = scope_data['hold_ratio']
            price = scope_data['price']
            pe_ttm = scope_data['pe_dt']
            mkv = scope_data['mkv']
            theme = scope_data['theme']
            pe_ttm = 200 if not pe_ttm else pe_ttm
            speed = roe_avg * roe_avg / pe_ttm
            data_dict = {
                "name": name,
                "code": stock,
                "price": price,
                "pe_ttm": pe_ttm,
                "roe_avg": roe_avg,
                "speed": round(speed, 3),
                "mkv": mkv,
                "holder_name": holder_name,
                "hold_ratio": hold_ratio,
                "industry": theme,
            }
            data_dict.update(scope_dict[str(stock)])
            stock_sort.append(data_dict)
    stock_sort = sorted(stock_sort, key=lambda x: x['speed'], reverse=True)[:]
    stock_sort = [{**item, "index": i +1} for i, item in enumerate(stock_sort)]

    ##  df = pd.DataFrame(stock_sort)
    ##  df.to_excel("../output/高增低估股票类/高增低估_250717.xlsx", index=False)

    ##for item in stock_sort[:250]:
        ##  print(item)

    return stock_sort[:300]



def get_stock_ids_count(name=None):
    sql = f"select ids, count(*) as count from stock_info " \
          f"where ids = (select ids from stock_info where name = '{name}' limit 1 )"
    sql_data = db_interface.stock_base.select(sql)
    res = {}
    data = list(sql_data)
    if data:
        data = data[0]
        res = {
            "ids": data[0],
            "count": data[1]
        }
    return res

def add_stock_holder(stock_list, mkv_value=15):
    sql = "select a.code, a.name, a.holder_name, a.hold_ratio, b.price, b.pe_ttm, b.mkv, b.ids from " \
          "(select code, name, holder_name, hold_ratio from stock_holder_name) a " \
          "inner join " \
          "(select code, price, ids, pe_ttm, mkv from stock_info where code in " + f"{tuple(stock_list)}" + ") b " \
                                                                                        "on a.code=b.code"
    # print(sql)
    query = db_interface.stock_base.select(sql)
    scope_dict = {}
    for item in query:
        code, name, holder_name, hold_ratio, price, pe_dt, mkv, theme = item
        if mkv and mkv >= mkv_value:
            scope_dict[code] = {
                "name": name,
                "holder_name": holder_name,
                "hold_ratio": hold_ratio,
                "price": price,
                "pe_dt": pe_dt,
                "mkv": mkv,
                "theme": theme
            }
    return scope_dict

## 股票所在行业的最有业绩：
def stock_industry_roe_rank(stock_name="保利地产"):
    ## 行业的绩优股
    r = have_stock_percent(type="name", stock_list=[stock_name])
    print(r)
    print('---------')
    ids_dict = get_stock_ids_count(name=stock_name)
    scope_dict = scope_condition_company(ids=ids_dict['ids'])
    d = stock_roe_rank(scope_dict)
    print(d)
    for i, item in enumerate(d):
        print(i, item)


## 所有股票的排行：
def stock_all_roe_rank():
    scope_dict = scope_condition_company(ids='白酒')
    r = stock_roe_rank()[:200]
    for i, item in enumerate(r):
        print(i, item)




if __name__ == "__main__":
    stock_roe_rank()








